IF OBJECT_ID('dbo.GetCSCRCaseManagers') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.GetCSCRCaseManagers
    IF OBJECT_ID('dbo.GetCSCRCaseManagers') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.GetCSCRCaseManagers >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.GetCSCRCaseManagers >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.GetCSCRCaseManagers
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 9/11/2007
--
-- Description: Returns list of CSCRCaseManagers from UDF table
--
-- Parameters:  None
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.GetCSCRCaseManagers()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 9/11/07     KC        Created
-- 9/20/07     KC        Added @ActivityType, changed source of loginid
-- 9/28/07     KC        Fixed FwkDomainUserId
--
-- =======================================================================
CREATE PROCEDURE dbo.GetCSCRCaseManagers
     (@ActivityType      VARCHAR(60)=NULL)
AS
BEGIN
     /*  Returned Fields for Dropdown
     --------------------------------
     PersonId            INT
     ManagerName         VARCHAR             
     RecomAbstractId     VARCHAR
     */

     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error            INT,
               @RowCount         INT
               
     -- Result Query    Use UDF Table to Limit Results. 
     --                 Also, PostAwardManager is not a "Role" per se
     SELECT DISTINCT P.Id AS PersonId,
               P.LastName + ', ' + P.FirstName as ManagerName,
               UDF.CSRCaseManager AS RecomAbstractId
     FROM dbo.InfoEdUDFProposal UDF
               INNER JOIN dbo.FwkDomainUser U ON UPPER(RTRIM(UDF.CSRCaseManager)) = UPPER(RTRIM(U.UserName))
               INNER JOIN dbo.AdmPerson P ON U.Id = P.FwkDomainUserId
     WHERE     (@ActivityType IS NULL OR UDF.ActivityType LIKE @ActivityType + '%')
     ORDER BY ManagerName
     
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.GetCSCRCaseManagers') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.GetCSCRCaseManagers >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.GetCSCRCaseManagers >>>'
go

GRANT EXECUTE ON dbo.GetCSCRCaseManagers TO InsightReport,Insight
go
